SQLS = {
    # 客户排名：统计每个客户的收费重、实际重量、CBM和泡比，按收费重降序
    "customer_ranking": """
         SELECT 
            username AS 用户姓名,
            SUM(chargeable_weight) AS 收费重,
            SUM(actual_weight) AS 实际重量,
            SUM(chargeable_volume) AS CBM,
            SUM(chargeable_weight)/SUM(chargeable_volume)  AS 泡比
        FROM 2025data
        WHERE week = '26'
        GROUP BY username
        ORDER BY 收费重 DESC
    """,
    # 你可以继续添加其他SQL
    # "other_api": "SELECT ... FROM ... WHERE ..."
    # 渠道占比：查询渠道名称和对应数值
    "channel_ratio": "SELECT name, value FROM channeldata",
    # 数据统计：查询近5周的重量、体积、包裹数
    "data_stat": "SELECT xAxis AS week, weight, volume, parcel_count FROM 5weekdate ORDER BY xAxis ASC",
    # 风险预警：查询客户、重量、上期重量、增长率
    "risk_alerts": "SELECT username, weight, lastweight, increase_ratio FROM 2025fxdata",
    # 客户数（周）：查询近5周的客户数
    "customer_count_week": "SELECT xAxis, data FROM 5weekuserdata ORDER BY xAxis ASC",
    # 客户数（月）：按月统计客户数
    "customer_count_month": "SELECT DATE_FORMAT(picking_time, '%Y-%m') AS month, COUNT(distinct username) AS count FROM shipments_data GROUP BY month ORDER BY month;",
    # 周度分析模块：以周为维度，统计前5个周的计费重、CBM、客户数
    # 参数：department（部门名，可为空），seller（销售人员名，可为空）
    # 过滤：status != 'cancelled'，为空字符串或NULL时不过滤
    # 返回：pt_week, total_chargeable_weight, total_cbm, customer_count
    "shipments_weekly_analysis": """
        SELECT pt_week, SUM(chargeable_weight) AS total_chargeable_weight, SUM(chargeable_volume) AS total_cbm, COUNT(DISTINCT username) AS customer_count
        FROM shipments_data
        WHERE status != 'cancelled'
          AND (%s IS NULL OR %s = '' OR seller_department_name = %s)
          AND (%s IS NULL OR %s = '' OR seller_name = %s)
        GROUP BY pt_week
        ORDER BY CAST(pt_week AS UNSIGNED) ASC
        LIMIT 5
    """,
    # 月度分析模块：以月为维度，统计所选月份范围内每月的计费重、CBM、客户数
    # 参数：start_month（起始月，两位字符串），end_month（结束月，两位字符串），department（部门名，可为空），seller（销售人员名，可为空）
    # 过滤：status != 'cancelled'，pt_month在范围内，且为空字符串或NULL时不过滤
    # 返回：pt_month, total_chargeable_weight, total_cbm, customer_count
    "shipments_monthly_analysis": """
        SELECT pt_month, SUM(chargeable_weight) AS total_chargeable_weight, SUM(chargeable_volume) AS total_cbm, COUNT(DISTINCT username) AS customer_count
        FROM shipments_data
        WHERE status != 'cancelled'
          AND CAST(pt_month AS UNSIGNED) >= %s AND CAST(pt_month AS UNSIGNED) <= %s
          AND (%s IS NULL OR %s = '' OR seller_department_name = %s)
          AND (%s IS NULL OR %s = '' OR seller_name = %s)
        GROUP BY pt_month
        ORDER BY CAST(pt_month AS UNSIGNED) ASC
    """,
    # 风险预警模块：以周为维度，统计前5周，按客户汇总计费重
    # 参数：department（部门名，可为空），seller（销售人员名，可为空）
    # 返回：客户（username）、前4周、前3周、前2周、本周业绩、上周业绩、同比率（本周/上周）
    # 说明：本SQL假设pt_week为连续数字，且数据已按时间顺序存储
    "shipments_risk_alerts_weekly": """
        SELECT
            t.username AS customer,
            SUM(CASE WHEN t.pt_week = @w1 THEN t.chargeable_weight ELSE 0 END) AS week_4,
            SUM(CASE WHEN t.pt_week = @w2 THEN t.chargeable_weight ELSE 0 END) AS week_3,
            SUM(CASE WHEN t.pt_week = @w3 THEN t.chargeable_weight ELSE 0 END) AS week_2,
            SUM(CASE WHEN t.pt_week = @w4 THEN t.chargeable_weight ELSE 0 END) AS last_week,
            SUM(CASE WHEN t.pt_week = @w5 THEN t.chargeable_weight ELSE 0 END) AS this_week,
            IFNULL(ROUND(SUM(CASE WHEN t.pt_week = @w5 THEN t.chargeable_weight ELSE 0 END) / NULLIF(SUM(CASE WHEN t.pt_week = @w4 THEN t.chargeable_weight ELSE 0 END),0), 2), 0) AS ratio
        FROM shipments_data t
        JOIN (
            SELECT DISTINCT pt_week FROM shipments_data WHERE status != 'cancelled' ORDER BY CAST(pt_week AS UNSIGNED) DESC LIMIT 5
        ) w ON t.pt_week = w.pt_week
        WHERE t.status != 'cancelled'
          AND (%s IS NULL OR %s = '' OR t.seller_department_name = %s)
          AND (%s IS NULL OR %s = '' OR t.seller_name = %s)
        GROUP BY t.username
    """,
    # 客户排名模块：以客户维度，统计每个客户的计费重、实际重量、CBM、泡比
    # 参数：department（部门名，可为空），seller（销售人员名，可为空）
    # 过滤：status != 'cancelled'，为空字符串或NULL时不过滤
    # 返回：username, total_chargeable_weight, total_actual_weight, total_cbm, volume_weight_radix
    "shipments_customer_ranking": """
        SELECT
            username,
            SUM(chargeable_weight) AS total_chargeable_weight,
            SUM(actual_weight) AS total_actual_weight,
            SUM(chargeable_volume) AS total_cbm,
            IFNULL(ROUND(SUM(chargeable_weight)/NULLIF(SUM(chargeable_volume),0), 2), 0) AS volume_weight_radix
        FROM shipments_data
        WHERE status != 'cancelled'
          AND (%s IS NULL OR %s = '' OR seller_department_name = %s)
          AND (%s IS NULL OR %s = '' OR seller_name = %s)
        GROUP BY username
        ORDER BY total_chargeable_weight DESC
    """,
    # 销售考核完成情况模块：以销售人员维度，统计2025年每月计费重，关联salesperson表
    # 参数：department（部门名，可为空），seller（销售人员名，可为空）
    # 过滤：只查在职销售（s.is_active=1），为空字符串或NULL时不过滤
    # 返回：销售人员、1-12月计费重、2024年销售额、2025年新增目标、2025总目标、完成率
    "shipments_sales_kpi": """
        SELECT
            s.salesperson_name,
            SUM(CASE WHEN d.pt_month = '01' THEN d.chargeable_weight ELSE 0 END) AS m1,
            SUM(CASE WHEN d.pt_month = '02' THEN d.chargeable_weight ELSE 0 END) AS m2,
            SUM(CASE WHEN d.pt_month = '03' THEN d.chargeable_weight ELSE 0 END) AS m3,
            SUM(CASE WHEN d.pt_month = '04' THEN d.chargeable_weight ELSE 0 END) AS m4,
            SUM(CASE WHEN d.pt_month = '05' THEN d.chargeable_weight ELSE 0 END) AS m5,
            SUM(CASE WHEN d.pt_month = '06' THEN d.chargeable_weight ELSE 0 END) AS m6,
            SUM(CASE WHEN d.pt_month = '07' THEN d.chargeable_weight ELSE 0 END) AS m7,
            SUM(CASE WHEN d.pt_month = '08' THEN d.chargeable_weight ELSE 0 END) AS m8,
            SUM(CASE WHEN d.pt_month = '09' THEN d.chargeable_weight ELSE 0 END) AS m9,
            SUM(CASE WHEN d.pt_month = '10' THEN d.chargeable_weight ELSE 0 END) AS m10,
            SUM(CASE WHEN d.pt_month = '11' THEN d.chargeable_weight ELSE 0 END) AS m11,
            SUM(CASE WHEN d.pt_month = '12' THEN d.chargeable_weight ELSE 0 END) AS m12,
            s.`2024sales_volume`,
            s.`2025additional_new`,
            s.`2025total_target`,
            IFNULL(ROUND(SUM(CASE WHEN d.pt_year = '2025' THEN d.chargeable_weight ELSE 0 END) / NULLIF(s.`2025total_target`,0), 2), 0) AS finish_rate
        FROM salesperson s
        LEFT JOIN shipments_data d
            ON s.salesperson_name = d.seller_name AND d.pt_year = '2025' AND d.status != 'cancelled'
        WHERE s.is_active = 1
          AND (%s IS NULL OR s.department_name = %s)
          AND (%s IS NULL OR s.salesperson_name = %s)
        GROUP BY s.salesperson_name, s.`2024sales_volume`, s.`2025additional_new`, s.`2025total_target`
        ORDER BY s.salesperson_name
    """,
    # 渠道占比模块：以服务渠道维度，统计每个服务渠道的计费重总数
    # 参数：department（部门名，可为空），seller（销售人员名，可为空）
    # 过滤：status != 'cancelled'，为空字符串或NULL时不过滤
    # 返回：servicer_channel, total_chargeable_weight
    "shipments_channel_ratio": """
        SELECT
            servicer_channel,
            SUM(chargeable_weight) AS total_chargeable_weight
        FROM shipments_data
        WHERE status != 'cancelled'
          AND (%s IS NULL OR %s = '' OR seller_department_name = %s)
          AND (%s IS NULL OR %s = '' OR seller_name = %s)
        GROUP BY servicer_channel
        ORDER BY total_chargeable_weight DESC
    """,
    # 业绩数据模块：统计本周、上周、本月、上月、本年计费重、CBM、客户数
    # 参数：this_week, last_week, this_month, last_month, this_year, department, seller
    # 过滤：status != 'cancelled'，为空字符串或NULL时不过滤
    # 返回：计费重、CBM、客户数（本周、上周、本月、上月、本年）
    "shipments_performance_data": """
        SELECT
            SUM(CASE WHEN pt_week = %s THEN chargeable_weight ELSE 0 END) AS weight_this_week,
            SUM(CASE WHEN pt_week = %s THEN chargeable_weight ELSE 0 END) AS weight_last_week,
            SUM(CASE WHEN pt_month = %s THEN chargeable_weight ELSE 0 END) AS weight_this_month,
            SUM(CASE WHEN pt_month = %s THEN chargeable_weight ELSE 0 END) AS weight_last_month,
            SUM(CASE WHEN pt_year = %s THEN chargeable_weight ELSE 0 END) AS weight_this_year,

            SUM(CASE WHEN pt_week = %s THEN chargeable_volume ELSE 0 END) AS cbm_this_week,
            SUM(CASE WHEN pt_week = %s THEN chargeable_volume ELSE 0 END) AS cbm_last_week,
            SUM(CASE WHEN pt_month = %s THEN chargeable_volume ELSE 0 END) AS cbm_this_month,
            SUM(CASE WHEN pt_month = %s THEN chargeable_volume ELSE 0 END) AS cbm_last_month,
            SUM(CASE WHEN pt_year = %s THEN chargeable_volume ELSE 0 END) AS cbm_this_year,

            COUNT(DISTINCT CASE WHEN pt_week = %s THEN username END) AS customer_this_week,
            COUNT(DISTINCT CASE WHEN pt_week = %s THEN username END) AS customer_last_week,
            COUNT(DISTINCT CASE WHEN pt_month = %s THEN username END) AS customer_this_month,
            COUNT(DISTINCT CASE WHEN pt_month = %s THEN username END) AS customer_last_month,
            COUNT(DISTINCT CASE WHEN pt_year = %s THEN username END) AS customer_this_year
        FROM shipments_data
        WHERE status != 'cancelled'
          AND (%s IS NULL OR %s = '' OR seller_department_name = %s)
          AND (%s IS NULL OR %s = '' OR seller_name = %s)
    """,
    # 获取在职销售人员，按部门分组，返回部门名称和销售人员列表
    "active_salespersons_by_department": """
        SELECT 
          IFNULL(department_name, '未知部门') AS department_name, 
          JSON_ARRAYAGG(JSON_OBJECT('value', salesperson_name, 'label', salesperson_name)) AS staff
        FROM salesperson
        WHERE is_active = 1
        GROUP BY IFNULL(department_name, '未知部门')
    """
}

